package com.xunshibao.core.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import com.xunshibao.core.page.PageUtils;
import com.xunshibao.core.page.Pagination;


public class BaseDAO {
	private static QueryRunner qr = new QueryRunner();
	/**
	 * 结果集为一个对象
	 * @param <T>
	 * @param c
	 * @param sql
	 * @param params
	 * @return
	 */
	public static <T> T queryForObject(Class<T> c, String sql, Object... params){
		Connection conn = ConnectionPool.getConnection();
		try {
			ResultSetHandler<T> rsh = new BeanHandler<T>(c);
//			QueryRunner qr = new QueryRunner();
			T rtn = (T)qr.query(conn, sql, rsh, params);
			return rtn;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally{
			ConnectionPool.closeConn(conn);
		}
	}
	/**
	 * 结果集为一个对象序列
	 * @param <T>
	 * @param c
	 * @param sql
	 * @param params
	 * @return
	 */
	public static <T> List<T> queryForList(Class<T> c, String sql, Object... params){
		Connection conn = ConnectionPool.getConnection();
		try {
			ResultSetHandler<List<T>> rsh = new BeanListHandler<T>(c);
//			QueryRunner qr = new QueryRunner();
			List<T> rtn = (List<T>)qr.query(conn, sql, rsh, params);
			return rtn;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally{
			ConnectionPool.closeConn(conn);
		}
	}
	/**
	 * 结果集为一行一列的，如记录总数，平均数等
	 * @param sql
	 * @param params
	 * @return
	 * 	失败返回null
	 */
	public static Object queryOneRowOneCol(String sql, Object... params){
		Connection conn = ConnectionPool.getConnection();
		try {
			QueryRunner qr = new QueryRunner();
			Object[] array = qr.query(conn, sql, new ArrayHandler(), params);
			if(array == null) {
				return null;
			} else {
				return array[0];
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally{
			ConnectionPool.closeConn(conn);
		}
	}
	/**
	 * 查询一列数据
	 * @param sql
	 * @param params
	 * @return
	 * 	失败返回null
	 */
	public static List<Object> queryForColumnList(String sql, Object... params){
		Connection conn = ConnectionPool.getConnection();
		try {
//			QueryRunner qr = new QueryRunner();
			List<Object> rtn = qr.query(conn, sql, new ColumnListHandler(), params);
			return rtn;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally{
			ConnectionPool.closeConn(conn);
		}
	}
	/**
	 * 返回的结果集每个map对应一行记录，key为列名
	 * @param sql
	 * @param params
	 * @return
	 * 	失败返回null
	 */
	public static List<Map<String, Object>> queryForMap(String sql, Object... params){
		Connection conn = ConnectionPool.getConnection();
		try {
//			QueryRunner qr = new QueryRunner();
			List<Map<String, Object>> rtn = qr.query(conn, sql, new MapListHandler(), params);
			return rtn;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally{
			ConnectionPool.closeConn(conn);
		}
	}
	/**
	 * 执行一条sql，主要是插入、修改、删除
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int execute(String sql, Object... params){
		Connection conn = ConnectionPool.getConnection();
		try {
//			QueryRunner qr = new QueryRunner();
			return qr.update(conn, sql, params);
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		} finally{
			ConnectionPool.closeConn(conn);
		}
	}
	public static void executeTransaction(Map<String, Object[]> map){
		Connection conn = ConnectionPool.getConnection();
		try {
			conn.setAutoCommit(false);
			Iterator<Entry<String, Object[]>> iterator = map.entrySet().iterator();
			Entry<String, Object[]> entry;
			while(iterator.hasNext()) {
				entry = iterator.next();
				if(entry.getValue() == null) {
					qr.update(conn, entry.getKey());
				} else {
					qr.update(conn, entry.getKey(), entry.getValue());
				}
			}
//			
//			Iterator<String> iter = map.keySet().iterator();
//			String sql;
////			QueryRunner qr;
//			Object[] params;
//			while (iter.hasNext()){
//				sql = iter.next();
//				params = map.get(sql);
////				qr = new QueryRunner();
//				if (params == null){
//					qr.update(conn, sql);
//				}else{
//					qr.update(conn, sql, params);
//				}
//			}
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally{
			try {
				conn.setAutoCommit(true);
			} catch (Exception e2) {
				e2.printStackTrace();
			}
			ConnectionPool.closeConn(conn);
		}
	}
	
	public static <T> Pagination page(Class<T> c, String sql, Object[] params, Integer pageNo, Integer pageSize) {
		String cntSql = PageUtils.convertPageSQL(sql);
		Number count = (Number) BaseDAO.queryOneRowOneCol(cntSql, params);
		Pagination page = new Pagination(pageNo, pageSize, count.intValue());
		
		String pageSql = PageUtils.genPageSQL(sql, page.getFirstResult(), pageSize);
		List<T> resList = BaseDAO.queryForList(c, pageSql, params);
		page.setList(resList);
		return page;
	}
	
	public static int update(String tableName, String[] properties, Object[] values, String condition) {
        StringBuilder buffer = new StringBuilder("update ");
        buffer.append(tableName);
        buffer.append(" set ");
        if(properties != null) {
            int i = 0;
            for(String column : properties) {
                if(i > 0) {
                    buffer.append(",");
                }
                buffer.append(column);
                buffer.append("=?");
                i++;
            }
        }
        if(condition != null) {
            buffer.append(" where ");
            buffer.append(condition);
        }
        return BaseDAO.execute(buffer.toString(), values);
    }
}
